"""
Case Type   : 行存压缩
Case Name   : 普通/分区/二级分区表，主机修改压缩参数，备机是否正常同步
Create At   : 2023/8/11
Owner       : li-xin12345
Description :
    1、建普通非压缩表，并插入数据，修改为压缩表
    2、建分区压缩表，并插入数据，修改压缩参数
    3、建二级分区压缩表，并插入数据，修改为非压缩表
    4、备机查看主机修改是否生效
    5、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
History     :
"""

import os
import unittest
import re

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

Pri_U_SH = CommonSH('PrimaryDbUser')
Node_Num = Pri_U_SH.get_node_num()


@unittest.skipIf(3 > Node_Num, '不满足一主两备环境跳过')
class RowCompressTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.std1_sh = CommonSH('Standby1DbUser')
        self.std2_sh = CommonSH('Standby2DbUser')
        self.constant = Constant()
        self.tb_name = ['t_alter_rowcompress_0080_01',
                        't_alter_rowcompress_0080_02',
                        't_alter_rowcompress_0080_03']
        self.create_compress_param = ['compresstype=1', 'compresstype = 2']
        self.alter_compress_param = ['compresstype=2, '
                                     'compress_byte_convert=true, '
                                     'compress_diff_convert=true, '
                                     'compress_level=16, '
                                     'compress_chunk_size=2048, '
                                     'compress_prealloc_chunks=3',
                                     'compresstype=2, '
                                     'compress_byte_convert=true, '
                                     'compress_diff_convert=true, '
                                     'compress_level=31, '
                                     'compress_chunk_size=2048, '
                                     'compress_prealloc_chunks=2',
                                     'compresstype=0']

    def test_row_compress(self):
        text = '-----step1:建普通非压缩表，并插入数据，修改为压缩表;expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.tb_name[0]} cascade;
        create table {self.tb_name[0]}(columnone integer, 
        columntwo char(50), columnthree varchar(50), columnfour integer, 
        columnfive char(50), columnsix varchar(50), columnseven char(50), 
        columneight char(50), columnnine varchar(50), columnten varchar(50), 
        columneleven char(50), columntwelve char(50), 
        columnthirteen varchar(50), columnfourteen char(50), 
        columnfifteem varchar(50));
        insert into {self.tb_name[0]} values(generate_series(0, 1000), 
        'test',substring(md5(random()::text), 1, 16), 
        2, substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16));
        '''
        sql_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      '执行失败：' + text)
        self.assertIn('INSERT 0 1001', sql_res, '执行失败：' + text)

        text = '---修改为压缩表---'
        self.log.info(text)
        sql = f'alter table {self.tb_name[0]} set ' \
              f'({self.alter_compress_param[0]}); checkpoint;' \
              f'\\d+ {self.tb_name[0]}'
        sql_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.CHECKPOINT_SUCCESS_MSG, sql_res,
                      '执行失败：' + text)
        self.assertIn(f'{self.alter_compress_param[0]}', sql_res,
                      '执行失败：' + text)

        text = '-----step2:建分区压缩表，并插入数据，修改压缩参数;expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.tb_name[1]} cascade;
        create table {self.tb_name[1]}
        (prod_id number(6),
         cust_id number,
         time_id date,
         channel_id char(1),
         promo_id number(6),
         quantity_sold number(3),
         amount_sold number(10,2)
        )
        with ({self.create_compress_param[0]})
        partition by range (time_id)
        interval('1 day')
        ( partition p1 values less than ('2023-02-01 00:00:00'),
          partition p2 values less than ('2023-02-02 00:00:00')
        );
        insert into {self.tb_name[1]} values(generate_series(0, 1000), 
        lpad(floor(random() * 1000)::text, 2, '0'), 
        '2023-01-10 00:00:00', 'a', 1, 1, 1);
        insert into {self.tb_name[1]} values(generate_series(0, 1000), 
        lpad(floor(random() * 1000)::text, 2, '0'), 
        '2023-02-01 00:00:00', 'a', 2, 2, 2);
        '''
        sql_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      '执行失败：' + text)
        self.assertIn('INSERT 0 1001', sql_res, '执行失败：' + text)
        self.assertEqual(sql_res.count('INSERT 0 1001'), 2, '执行失败：' + text)

        text = '---修改压缩参数---'
        self.log.info(text)
        sql = f'alter table {self.tb_name[1]} set ' \
              f'({self.alter_compress_param[1]}); checkpoint;' \
              f'\\d+ {self.tb_name[1]}'
        sql_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.CHECKPOINT_SUCCESS_MSG, sql_res,
                      '执行失败：' + text)
        self.assertIn(f'{self.alter_compress_param[1]}', sql_res,
                      '执行失败：' + text)

        text = '-----step3:建二级分区压缩表，并插入数据，修改为非压缩表;' \
               'expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.tb_name[2]} cascade;
        create table {self.tb_name[2]}
        (
            month_code varchar2 ( 30 ) not null ,
            dept_code  varchar2 ( 30 ) not null ,
            user_no    varchar2 ( 30 ) not null ,
            sales_amt  int
        )
        with ({self.create_compress_param[1]})
        partition by list (month_code) subpartition by list (dept_code)
        (
          partition p_201901 values ( '201902' )
          (
            subpartition p_201901_a values ( '1' ),
            subpartition p_201901_b values ( '2' )
          ),
          partition p_201902 values ( '201903' )
          (
            subpartition p_201902_a values ( '1' ),
            subpartition p_201902_b values ( '2' )
          )
        );
        insert into {self.tb_name[2]} values('201902', '1', '1', 1);
        insert into {self.tb_name[2]} values('201902', '2', '1', 1);
        insert into {self.tb_name[2]} values('201902', '1', '1', 1);
        insert into {self.tb_name[2]} values('201903', '2', '1', 1);
        insert into {self.tb_name[2]} values('201903', '1', '1', 1);
        insert into {self.tb_name[2]} values('201903', '2', '1', 1);
        '''
        sql_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      '执行失败：' + text)
        self.assertIn('INSERT 0 1', sql_res, '执行失败：' + text)
        self.assertEqual(sql_res.count('INSERT 0 1'), 6, '执行失败：' + text)

        text = '---改为非压缩表-----'
        self.log.info(text)
        sql = f'alter table {self.tb_name[2]} set ' \
              f'({self.alter_compress_param[2]}); checkpoint;' \
              f'\\d+ {self.tb_name[2]}'
        sql_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.CHECKPOINT_SUCCESS_MSG, sql_res,
                      '执行失败：' + text)
        self.assertIn(f'{self.alter_compress_param[2]}', sql_res,
                      '执行失败：' + text)

        text = '-----step4:备机查看主机修改是否生效;expect:成功-----'
        self.log.info(text)
        for i in range(3):
            sql = f'\\d+ {self.tb_name[i]}'
            std1_res, std2_res = self.std1_sh.execut_db_sql(sql), \
                                 self.std2_sh.execut_db_sql(sql)
            self.log.info(std1_res)
            self.log.info(std2_res)
            self.assertIn(f'{self.alter_compress_param[i]}', std1_res,
                          '执行失败：' + text)
            self.assertIn(f'{self.alter_compress_param[i]}', std2_res,
                          '执行失败：' + text)

    def tearDown(self):
        text = '-----step5:清理环境; expect:成功-----'
        self.log.info(text)
        drop_res = []
        for k, v in enumerate(self.tb_name):
            sql = f'drop table if exists {v};'
            drop_res.append(Pri_U_SH.execut_db_sql(sql))
            self.log.info(drop_res)
            self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_res[k],
                          '执行失败：' + text)
            k += 1
        self.assertEqual(
            sum(1 for s in drop_res if re.search(r'DROP\s+TABLE', s)), 3,
            '执行失败：' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')